import pandas as pd
from datetime import datetime, timedelta
from collections import defaultdict
df = pd.read_excel('料堆明细.xlsx')
header = list(df.columns)
data_list = df.values.tolist()
now = datetime.now()
yesterday = now - timedelta(days=1)
yesterday_str = yesterday.strftime('%Y-%m-%d 00:00:00')
yesterday_list = [item for item in data_list if item[0] == yesterday_str]

# yesterday_df = pd.read_excel('yesterday.xlsx')
# yesterday_list = yesterday_df.values.tolist()
# header = list(yesterday_df.columns)
header.append('单价')
response = list([])
for item in yesterday_list:
    total = float(item[-1])
    weight = float(item[-2])
    if total != 0 and weight != 0:
        price = total/weight
        if price < 2000:
            item.append(price)
            response.append(item)

grouped_data = defaultdict(list)
for child in response:
    key = child[1]
    grouped_data[key].append(child)
grouped_lists = list(grouped_data.values())
for group in grouped_lists:
    filtered_df = pd.DataFrame(group, columns=header)
    filtered_df.to_excel('response/{}.xlsx'.format(group[0][1]), index=False)

print(len(response))
